package com.example.demo.controller;

import java.io.*;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import com.example.demo.annotation.AutoLog;
import org.apache.commons.lang3.ObjectUtils;
import org.postgresql.core.BaseConnection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.example.demo.entity.BaseResponse;
import com.example.demo.entity.Database;
import com.example.demo.entity.Student;
import com.example.demo.entity.Teacher;
import com.example.demo.service.DatabaseService;
import com.example.demo.service.StudentService;
import com.example.demo.service.TeacherService;
import com.example.demo.tool.DBConnection;
import com.example.demo.tool.DBDatasource;
import com.example.demo.tool.DBExecTool;
import com.example.demo.tool.DBFileIOTool;
import com.example.demo.tool.DBMetaDataTool;
import com.example.demo.util.EncodeDetector;
import com.example.demo.util.FileTrans;
import com.example.demo.util.JSQLParserUtils;
import com.example.demo.util.LocalCacheUtil;
import com.example.demo.util.OutputToInput;
import com.example.demo.util.RemoveComment;
import com.example.demo.util.SQLTerminalQuerySplit;
import com.example.demo.util.SetDBConnection;
import com.opencsv.exceptions.CsvException;
import com.example.demo.util.EncodingDetect;


@RequestMapping("/practice")

@RestController
public class PracticeController {
    @Autowired
    private DatabaseService databaseService;
    @Autowired
    private TeacherService teacherService;
    @Autowired
    private StudentService studentService;
    @Autowired
    private DBDatasource dbDatasource;

    @AutoLog("连接教师实践数据库")
    @PostMapping(value = "/connectForTeacher")
    public BaseResponse<Map<String, Object>> connectForTeacher(@RequestParam(value = "teacherId") String teacherId,
                                                               @RequestParam(value = "zjId", required = false, defaultValue = "#") String zjId,
                                                               @RequestParam(value = "dbName") String dbName,
                                                               @RequestParam(value = "schemaName", required = false) String schemaName) {
        Map<String, Object> modelMap = new HashMap<>();

        //获取数据库连接信息
        Database database = databaseService.getDatabaseByName(dbName);
        if (database == null) {
            modelMap.put("errmessage", dbName + " 数据库不存在");
            return BaseResponse.fail(modelMap);
        }
        if (!database.getUserId().equals(teacherId)) {
            modelMap.put("errmessage", dbName + " 数据库不属于 " + teacherId);
            return BaseResponse.fail(modelMap);
        }

        //获取数据库对应的教师用户信息
        Teacher teacher = teacherService.getTeacher(teacherId);

        //建立数据库连接
        DBConnection dbConnection = new DBConnection(dbDatasource);
        SetDBConnection.setDBConnection(dbConnection, database, teacher);
        try {
            dbConnection.getConnect();
        } catch (SQLException e) {
            e.printStackTrace();
//			modelMap.put("errmessage", "database connect error");
            modelMap.put("errmessage", "数据库连接错误");
            return BaseResponse.fail(modelMap);
        }

        //如果存在旧连接，关闭旧连接，将新连接存入缓存中
        DBConnection oldDbConnection = (DBConnection) LocalCacheUtil.get(teacherId);
        if (oldDbConnection != null) {
            try {
                oldDbConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            LocalCacheUtil.remove(teacherId);
        }
        if (zjId.equals("#"))
            LocalCacheUtil.set(teacherId, dbConnection, 4 * 60 * 60 * 1000);
        else
            LocalCacheUtil.set(zjId, dbConnection, 4 * 60 * 60 * 1000);

//		modelMap.put("notice", "database connect successfully");
        modelMap.put("notice", "数据库连接成功");
        return BaseResponse.success(modelMap);
    }

    @AutoLog("连接学生实践数据库")
    @PostMapping(value = "/connectForStudent")
    public BaseResponse<Map<String, Object>> connectForStudent(@RequestParam(value = "studentId") String studentId,
                                                               @RequestParam(value = "dbName", required = false) String dbName,
                                                               @RequestParam(value = "username", required = false) String username,
                                                               @RequestParam(value = "password", required = false) String password) {
        Map<String, Object> modelMap = new HashMap<>();

        //判断学生是否存在，并获取相应的连接参数
        Student student = studentService.getStudent(studentId);
        System.out.println(student);
        if (student == null || !student.isIsactive()) {
            modelMap.put("errmessage", " 学生不存在");
            return BaseResponse.fail(modelMap);
        }
        //判断数据库是否存在
        Database database = databaseService.getDatabaseByName(student.getDbName());
        System.out.println(database);
        if (database == null) {
            modelMap.put("errmessage", dbName + " 数据库不存在");
            return BaseResponse.fail(modelMap);
        }
        //建立数据库连接
        DBConnection dbConnection = new DBConnection(dbDatasource);
        dbConnection.setDatabase(student.getDbName());
        dbConnection.setUsername(student.getUsername());
        dbConnection.setPassword(student.getPassword());

        try {
            dbConnection.getConnect();
        } catch (SQLException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "数据库连接错误");
            return BaseResponse.fail(modelMap);
        }

        //如果存在旧连接，关闭旧连接，将新连接存入缓存中
        DBConnection oldDbConnection = (DBConnection) LocalCacheUtil.get(studentId);
        if (oldDbConnection != null) {
            try {
                oldDbConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            LocalCacheUtil.remove(studentId);
        }
        LocalCacheUtil.set(studentId, dbConnection, 4 * 60 * 60 * 1000);

//		modelMap.put("notice", "database connect successfully");
        modelMap.put("ww", "数据库连接成功");
        return BaseResponse.success(modelMap);
    }

    @AutoLog("断开实践数据库")
    @PostMapping(value = "/disconnect")
    public BaseResponse<Object> disconnect(@RequestParam(value = "userId") String userId) {
        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);
        if (dbConnection != null) {
            LocalCacheUtil.remove(userId);
            try {
                dbConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
//			LocalCacheUtil.remove(userId);
        }

        return BaseResponse.success(null);
    }

    @AutoLog("实践数据库--执行SQL")
    @PostMapping(value = "/execCode")
    public BaseResponse<Map<String, Object>> execCode(@RequestParam(value = "userId") String userId,
                                                      @RequestParam(value = "code") String code,
                                                      @RequestParam(value = "isexplain") boolean isexplain) {
        Map<String, Object> modelMap = new HashMap<>();
        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);
        System.out.println(LocalCacheUtil.get(userId));
        if (dbConnection == null) {
            modelMap.put("errmessage", "连接超时, 请重连数据库");
            return BaseResponse.fail(modelMap);
        }

        try {
            Connection connection = dbConnection.getConnect();
            DBExecTool dbExecTool = new DBExecTool(connection);
            ArrayList<String> queryList = new ArrayList<>();
            List<Map<String, Object>> resultList = new ArrayList<>();
            SQLTerminalQuerySplit sqlTerminalQuerySplit = new SQLTerminalQuerySplit();
            RemoveComment removeComment = new RemoveComment();


            sqlTerminalQuerySplit.splitQuerries(queryList, code, false);
            int n = queryList.size();
            if (n > 1024) {
                modelMap.put("errmessage", "执行语句大于1024条，请检查后重新输入");
                return BaseResponse.fail(modelMap);
            }
            for (String singleSQL : queryList) {
                singleSQL = removeComment.removeComment(singleSQL).trim();
                if (isexplain)
                    singleSQL = "explain " + singleSQL;

                if (singleSQL.isEmpty()) continue;
                Map<String, Object> sqlResult = dbExecTool.execute(singleSQL);
                sqlResult.put("supportUpdate", JSQLParserUtils.isQueryResultEditSupported(singleSQL));
                sqlResult.put("isexplain", isexplain);

                resultList.add(sqlResult);
//				if (sqlResult.get("ResultType").equals("ERROR")) 
//					break;
            }
            modelMap.put("resultList", resultList);
            System.out.println(resultList);
        } catch (SQLException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "数据库连接错误");
            return BaseResponse.fail(modelMap);
        }

        return BaseResponse.success(modelMap);
    }

    @AutoLog("获取模式")
    @PostMapping(value = "/getUserSchema")
    public BaseResponse<Map<String, Object>> getUserSchema(@RequestParam(value = "userId") String userId) {
        Map<String, Object> modelMap = new HashMap<>();
        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);
        if (dbConnection == null) {
//			modelMap.put("errmessage", "connection timeout, please reconnect database");
            modelMap.put("errmessage", "连接超时, 请重连数据库");
            return BaseResponse.fail(modelMap);
        }
        try {
            Connection connection = dbConnection.getConnect();
            DBMetaDataTool dbMetaDataTool = new DBMetaDataTool(connection);
            List<Map<String, Object>> userSchemaList = dbMetaDataTool.getUserSchemas();
            modelMap.put("currentSchemaName", connection.getSchema());
            modelMap.put("userSchemaList", userSchemaList);
        } catch (SQLException e) {
            e.printStackTrace();
//			modelMap.put("errmessage", "database error");
            modelMap.put("errmessage", "数据库错误");
            modelMap.put("detail", e.getMessage());
            return BaseResponse.fail(modelMap);
        }

        return BaseResponse.success(modelMap);
    }

    @AutoLog("获取模式元信息")
    @PostMapping(value = "/getSchemaMetadata")
    public BaseResponse<Map<String, Object>> getSchemaMetadata(@RequestParam(value = "userId") String userId,
                                                               @RequestParam(value = "schemaOid") long schemaOid) {
        Map<String, Object> modelMap = new HashMap<>();
        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);
        if (dbConnection == null) {
            modelMap.put("errmessage", "连接超时, 请重连数据库");
            return BaseResponse.fail(modelMap);
        }

        try {
            Connection connection = dbConnection.getConnect();
            DBMetaDataTool dbMetaDataTool = new DBMetaDataTool(connection);
            Map<String, Object> schemaMetaDataMap = dbMetaDataTool.getSchemaMetaData(schemaOid);
            modelMap.put("schemaOid", schemaOid);
            modelMap.put("schemaMetaDataMap", schemaMetaDataMap);
        } catch (SQLException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "数据库错误");
            modelMap.put("detail", e.getMessage());
            return BaseResponse.fail(modelMap);
        }

        return BaseResponse.success(modelMap);
    }

    @PostMapping(value = "/judgefileEncoder")
    public BaseResponse<Map<String, Object>> judgefileEncoder(@RequestParam(value = "file") MultipartFile file) {
        Map<String, Object> modelMap = new HashMap<>();
        if (ObjectUtils.isEmpty(file) || file.getSize() <= 0) {
            modelMap.put("errmessage", "文件为空,无法进行判断");
            return BaseResponse.fail(modelMap);
        }
        String fileName = file.getOriginalFilename();
        if ("".equals(fileName)) {
            fileName = file.getName();
        }
        File file1 = new File(fileName);
        OutputStream out = null;
        try {
            //获取文件流，以文件流的方式输出到新文件
//    InputStream in = multipartFile.getInputStream();
            out = new FileOutputStream(file1);
            byte[] ss = file.getBytes();
            for (int i = 0; i < ss.length; i++) {
                out.write(ss[i]);
            }
        } catch (IOException e) {
            modelMap.put("errmessage", "从文件中无法得到输入流");
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        String filecode = EncodingDetect.getJavaEncode(file1);
        System.out.println(filecode);
        modelMap.put("文件编码格式", filecode);
        return BaseResponse.success(modelMap);
    }

    @AutoLog("上传表")
    @PostMapping(value = "/uploadTable")
    public BaseResponse<Map<String, Object>> uploadTable(@RequestParam(value = "userId") String userId,
                                                         @RequestParam(value = "file") MultipartFile file,
                                                         @RequestParam(value = "tableName") String tableName,
                                                         @RequestParam(value = "colNames", required = false) ArrayList<String> colNames,
                                                         @RequestParam(value = "header", required = false, defaultValue = "true") boolean header
    ) {
        Map<String, Object> modelMap = new HashMap<>();
        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);
        if (dbConnection == null) {
            modelMap.put("errmessage", "连接超时, 请重连数据库");
            return BaseResponse.fail(modelMap);
        }

        //判断文件格式类型，xls和xlsx调用相应接口将其转为csv
        String fileName = file.getOriginalFilename();
        if ("".equals(fileName)) {
            fileName = file.getName();
        }

        InputStream fileInput;
        try {
            fileInput = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "从文件中无法得到输入流");
            return BaseResponse.fail(modelMap);
        }

        InputStream csvInput;
        if (fileName.matches("^.+\\.(?i)(xls)$")) {
            try {
                ByteArrayOutputStream csvOutput = new ByteArrayOutputStream();
                FileTrans.XLS2CSV(fileInput, csvOutput);
                csvInput = OutputToInput.OutputToInput(csvOutput);
            } catch (IOException e) {
                e.printStackTrace();
                try {
                    fileInput.close();
                } catch (IOException e2) {
                    e2.printStackTrace();
                }
                modelMap.put("errmessage", "无法将 xls文件 转换成 csv文件");
                return BaseResponse.fail(modelMap);
            }
        } else if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            try {
                ByteArrayOutputStream csvOutput = new ByteArrayOutputStream();
                FileTrans.XLSX2CSV(fileInput, csvOutput);
                csvInput = OutputToInput.OutputToInput(csvOutput);
            } catch (IOException e) {
                e.printStackTrace();
                try {
                    fileInput.close();
                } catch (IOException e2) {
                    e2.printStackTrace();
                }
                modelMap.put("errmessage", "无法将 xlsx文件 转换成 csv文件");
                return BaseResponse.fail(modelMap);
            }
        } else if (fileName.matches("^.+\\.(?i)(csv)$")) {
//			csvInput = fileInput;	
            try {
                ByteArrayOutputStream csvOutput = new ByteArrayOutputStream();
                FileTrans.transUTF8(fileInput, csvOutput, EncodingDetect.getJavaEncode2(file));
                csvInput = OutputToInput.OutputToInput(csvOutput);

            } catch (IOException e) {
                e.printStackTrace();
                try {
                    fileInput.close();
                } catch (IOException e2) {
                    e2.printStackTrace();
                }
                modelMap.put("errmessage", "can't transfer csv to UTF8");
                modelMap.put("errmessage", "无法将 csv文件 转换成 UTF8文件");
                return BaseResponse.fail(modelMap);
            }
        } else {
            try {
                fileInput.close();
            } catch (IOException e2) {
                e2.printStackTrace();
            }
//			modelMap.put("errmessage", "unrecognized file type");
            modelMap.put("errmessage", "未识别文件类型");
            return BaseResponse.fail(modelMap);
        }

        //将csv文件导入数据库中
        try {
            BaseConnection baseConnection = dbConnection.getPgConnect();
            DBFileIOTool dbFileIOTool = new DBFileIOTool(baseConnection);
            dbFileIOTool.fileToDatabase(tableName, colNames, csvInput, header);
            baseConnection.close();
            modelMap.put("status", "success");
        } catch (SQLException | ClassNotFoundException | IOException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "无法将csv文件导入数据库");
            modelMap.put("detail", e.getMessage());
            return BaseResponse.fail(modelMap);
        } finally {
            try {
                csvInput.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return BaseResponse.success(modelMap);
    }

    @AutoLog("下载表")
    @PostMapping(value = "/downloadTable")
    public BaseResponse<Map<String, Object>> loadTable(@RequestParam(value = "userId") String userId,
                                                       @RequestParam(value = "fileType") String fileType,
                                                       @RequestParam(value = "tableName") String tableName,
                                                       @RequestParam(value = "colNames", required = false) ArrayList<String> colNames,
                                                       @RequestParam(value = "header", required = false, defaultValue = "false") boolean header,
                                                       HttpServletResponse response) {
        Map<String, Object> modelMap = new HashMap<>();
        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);
        if (dbConnection == null) {
            modelMap.put("errmessage", "连接超时, 请重连数据库");
            return BaseResponse.fail(modelMap);
        }

        ByteArrayOutputStream csvOutput = new ByteArrayOutputStream();
        //从数据库中导出csv文件流
        try {
            BaseConnection baseConnection = dbConnection.getPgConnect();
            DBFileIOTool dbFileIOTool = new DBFileIOTool(baseConnection);
            dbFileIOTool.fileFromDatabase(tableName, colNames, csvOutput, header);
            baseConnection.close();
        } catch (SQLException | ClassNotFoundException | IOException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "无法将csv文件导入数据库");
            return BaseResponse.fail(modelMap);
        }

        //根据传入文件类型，将csv文件流转为相应的文件类型
        String fileName = tableName;
        ByteArrayOutputStream responseOutput = new ByteArrayOutputStream();
        try {
            if ("csv".equals(fileType)) {
                fileName = tableName + ".csv";
                csvOutput.writeTo(responseOutput);
            } else if ("xls".equals(fileType)) {
                fileName = tableName + ".xls";
                FileTrans.CSV2XLS(OutputToInput.OutputToInput(csvOutput), responseOutput);
            } else if ("xlsx".equals(fileType)) {
                fileName = tableName + ".xlsx";
                FileTrans.CSV2XLSX(OutputToInput.OutputToInput(csvOutput), responseOutput);
            } else {
                modelMap.put("errmessage", "未识别文件类型");
                return BaseResponse.fail(modelMap);
            }
        } catch (IOException | CsvException e) {
            e.printStackTrace();
            try {
                responseOutput.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            modelMap.put("errmessage", "无法转换该文件");
            return BaseResponse.fail(modelMap);
        } finally {
            try {
                csvOutput.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        //输出到HttpResponse响应流
        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            responseOutput.writeTo(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
//			modelMap.put("errmessage", "can't download this file");
            modelMap.put("errmessage", "无法下载该文件");
            return BaseResponse.fail(modelMap);
        } finally {
            try {
                responseOutput.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }

        return null;
    }

    @AutoLog("实践模块--取消执行SQL")
    //采用关闭连接的方式取消当前执行的全部SQL语句
    @PostMapping(value = "/cancelAll")
    public BaseResponse<Map<String, Object>> cancelAll(@RequestParam(value = "userId") String userId) {
        Map<String, Object> modelMap = new HashMap<String, Object>();
        DBConnection dbConnection = (DBConnection)
                LocalCacheUtil.get(userId);
        if (dbConnection == null) {
            modelMap.put("errmessage", "connection timeout, please reconnect database");
            return BaseResponse.fail(modelMap);
        }
        try {
            if (!dbConnection.cancelAll()) {
                modelMap.put("errmessage", "取消失败或当前未执行SQL语句");
                return BaseResponse.fail(modelMap);
            }
            return BaseResponse.success(null);
        } catch (SQLException e) {
            e.printStackTrace();
            modelMap.put("errmessage", "database connect error");
            return BaseResponse.fail(modelMap);
        }
    }

}
